package bookManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO 这是在java代码中一个非常常见的操作
 * Data Access Object
 * 用来访问数据的对象，BookDao这是一个对象或者一个类，这个类用来访问Book对象
 * 这个类负责提供对book表的 增删改查 方法
 * 接下来实现这个类的细节，这些的细节，就是纯粹的JDBC操作
 */
public class BookDao {

    //新增一本书,插入
    public void add(Book book) {
        //1、和数据库建立连接
        Connection connection=null;
        PreparedStatement statement=null;
        try {
             connection=DBUtil.getConnection();
            //2、构造SQL语句
            String sql="insert into book values(null,?,?,?,?,0)";
            statement=connection.prepareStatement(sql);
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());

            //3、执行sql
            int ret=statement.executeUpdate();
            if (ret!=1) {
                System.out.println("新增书籍失败");
            } else {
                System.out.println("新增书籍成功！");
            }

            //4、进行收尾，如果把close放在这里不合适，一旦上面的代码抛出异常，此时close就可能执行不到
            //DBUtil.close(null,connection,statement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();

        } finally {
            //4、进行收尾
            DBUtil.close(null,connection,statement);
        }
    }


    //查找所有书
    public List<Book> findBooks() {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Book> books=new ArrayList<>();
        try {
            //1、建立连接
            connection=DBUtil.getConnection();
            //2、构造sql
            String sql="select * from book";
            statement=connection.prepareStatement(sql);
            //3、执行sql
            resultSet=statement.executeQuery();

            while (resultSet.next()) {
                Book book=new Book();
                //循环里面获取到每一行，再获取每一列；
                int bookId=resultSet.getInt("bookId");
                book.setBookId(bookId);//获取到bookId在设置到book对象里面；
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed")==1?true:false);

                //一系列对book复赋值
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet,connection,statement);
        }
        return books;
    }

    //根据书名来查找书
    public List<Book> findBooksByName(String name) {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Book> books=new ArrayList<>();
        try {
            connection=DBUtil.getConnection();

            String sql="select * from  book where name = ?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);

            resultSet=statement.executeQuery();

            while(resultSet.next()) {
                Book book=new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed")==1?true:false);
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet,connection,statement);
        }

        return books;
    }

    //删除操作
    public  boolean delete(int bookId) {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from book where bookId = ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            int ret=statement.executeUpdate();
            if (ret!=1) {
                return false;
            }
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,connection,statement);
        }
        return false;
    }


    //借书
    //使用true表示删除成功，false表示删除失败
    //把 isBorrowed 这一例，设置成“已借出”
    // 能否针对一本已经被借出的书, 再借一次?
    // 显然不行, 需要先判定书是不是借出. 未借出, 再执行借出操作.
    public boolean BorrowBook(int bookId) {
        Connection connection=null;
        PreparedStatement statement=null;
        PreparedStatement statement2 = null;
        ResultSet resultSet=null;
        try {
            //1、建立连接
            connection=DBUtil.getConnection();
            //2、构造sql
            String sql="select * from book where bookId = ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            //3、执行SQL
            resultSet=statement.executeQuery();
            //4、检查查询结果
            if (resultSet.next()) {
                //能查到，进一步判定借出的状态，
                boolean isBorrow=(resultSet.getInt("isBorrowed")==1);
                if(isBorrow) {
                    System.out.println("书已经被借出了！ bookId："+ bookId);
                    return false;
                }
            } else {
                //没查到，书不存在，借出失败
                System.out.println("书不存在! bookId = " + bookId);
                return false;
            }
            // 5. 执行借书操作. 构造一个 修改 语句
            sql = "update book set isBorrowed = 1 where bookId = ?";
            statement2 = connection.prepareStatement(sql);//这里是第二个statement对象，于是第一个statement对象就没了，所以找另外的引用执行它
            statement2.setInt(1, bookId);

            int ret = statement2.executeUpdate();
            if (ret != 1) {
                System.out.println("修改借出状态失败! bookId = " + bookId);
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            //DBUtil.close(resultSet,connection,statement);
            //因为有两个statement引用对象，所以只能手动关资源了
            //GC————垃圾回收
            if (resultSet!=null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement2!=null) {
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return false;
    }




    //还书
    //使用true表示删除成功，false表示删除失败
    //把isBorrowed这一列，设置成“未借出”
    public boolean returnBook(int bookId) {
        Connection connection=null;
        PreparedStatement statement=null;
        PreparedStatement statement2 = null;
        ResultSet resultSet=null;
        try {
            //1、建立连接
            connection=DBUtil.getConnection();
            //2、构造sql
            String sql="select * from book where bookId = ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            //3、执行SQL
            resultSet=statement.executeQuery();
            //4、检查查询结果
            if (resultSet.next()) {
                //能查到，进一步判定借出的状态，
                boolean isBorrow=(resultSet.getInt("isBorrowed")==1);//true
                if(!isBorrow) {//false
                    System.out.println("书已经归还了！ bookId："+ bookId);
                    return false;
                }
            } else {
                //没查到，书不存在，归还失败
                System.out.println("书不存在! bookId = " + bookId);
                return false;
            }
            // 5. 执行还书操作. 构造一个 修改 语句
            sql = "update book set isBorrowed = 0 where bookId = ?";
            statement2 = connection.prepareStatement(sql);//这里是第二个statement对象，于是第一个statement对象就没了，所以找另外的引用执行它
            statement2.setInt(1, bookId);

            int ret = statement2.executeUpdate();
            if (ret != 1) {
                System.out.println("修改借出状态失败! bookId = " + bookId);
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            //DBUtil.close(resultSet,connection,statement);
            //因为有两个statement引用对象，所以只能手动关资源了
            //GC————垃圾回收
            if (resultSet!=null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement2!=null) {
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

}
